<? // lookup - lookup according to fk

// arguments:
// - el - name of calling item
// - lookup - lookup structure
//   = fkcol - name of foreign key col (from here use fk to get dd to foreign table)
//   = match - whether exact or similar match
//   = cols - renderCol/queryCol pairs (if not supplied, pull from dd)
// - args - standard post args

//-require_once( '../ajax/dd.php' );
$dd = unserialize(file_get_contents('../ajax/dd.ser'));

$el = $_GET['el'];
list($pre,$idx,$col) = explode(':',$el);
$lookup = $_GET['lookup'];
$args = $_GET['args'];

// find dd key
$rel = @$args[$pre]['dd'];
if( !$rel && @$dd[$pre] ) $rel = $pre;
if( !$rel || !$dd[$rel] ) ajax_exit( 'failure', "unable to find dd for $pre" );

// variables
$fkcol = $lookup['fkcol'];
if( !$fkcol ) ajax_exit( 'failure', "unable to find fkcol" );
$val = @$args[$pre]['rows'][$idx]['cols'][$col]['value'];
if( !$val ) ajax_exit( 'failure', "unable to find val in args $pre $idx $col" );
$fk = $dd[$rel]['cols'][$fkcol]['fk'];
if( !$fk ) ajax_exit( 'failure', "unable to find fk" );

// now fk dd
$frel = @$fk['dd'];
if( !$frel ) ajax_exit( 'failure', "unable to find fk dd" );

// $build queries
$queryArr = Array();

// match on val
$like = ( strpos($val,'*')!==false );
if( $like ) $val = str_replace('*','%',$val);

// build columns to query - if in lookup, use them; otherwise from dd
$cols = Array( "{$fk['pkcol']} as $fkcol" );
foreach( (isset($lookup['cols'])?$lookup['cols']:$fk['cols']) as $pair ) $cols[] = "{$pair['queryCol']} as {$pair['renderCol']}";
$basequery = "select ".implode(', ',$cols)." from {$dd[$frel]['tbl_v']} ";

// like
$wheres = Array();
foreach( $dd[$frel]['lookups'] as $col=>$type ) if( $like ) $wheres[] = "( '$val' ilike $col )";
if( count($wheres) ) $queryArr[] = "$basequery where ".implode(' or ',$wheres);

// exact
$wheres = Array();
foreach( $dd[$frel]['lookups'] as $col=>$type ) if( $type == 'exact' && !$like ) $wheres[] = "( upper($col) = upper('$val') )";
if( count($wheres) ) $queryArr[] = "$basequery where ".implode(' or ',$wheres);

// partial
$wheres = Array();
foreach( $dd[$frel]['lookups'] as $col=>$type ) if( $type == 'exact' && !$like ) $wheres[] = "( '%{$val}%' ilike $col )";
if( count($wheres) ) $queryArr[] = "$basequery where ".implode(' or ',$wheres);

// try each query
foreach( $queryArr as $query ) {

	$rows = db_get_rcds( $query );
	if( count($rows) == 1 ) ajax_exit( 'success', 'one row exact match', Array('rows'=>$rows) );
	if( count($rows) > 1 ) {
		$msg = "many matches";
		foreach( $row as $rows ) $msg .= "\n{$row['name']}";
		ajax_exit( 'failure', "many matchs $msg" );
	}
	else	ajax_exit( 'failure', "query: $query" );
}

ajax_exit( 'failure', "not match in {$dd[$frel]['name']} using \"$val\"" );

?>
